const XLSX = require("xlsx");

function importExcel(filePath, callback) {
  var data = [];
  var err = null;
  try {
    // Everything went fine
    var workbook = XLSX.readFile(filePath);　//整个　excel　文档
    var sheetNames = workbook.SheetNames; //获取所有工作薄名

    //console.log(sheetNames);

    //解析
    var sheet1 = workbook.Sheets[sheetNames[0]]; //根据工作薄名获取工作薄

    /*
     sheet1['!ref']　获取工作薄的有效范围　'A1:C20'
     XLSX.utils.decode_range 将有效范围转为　range对象
     range: {s: {r:0, c:0}, e: {r:10, 3}}
     */
    var range = XLSX.utils.decode_range(sheet1['!ref']);

    //循环获取单元格值
    for (var R = range.s.r; R <= range.e.r; ++R) {
      var row = [], flag = false;
      for (var C = range.s.c; C <= range.e.c; ++C) {
        var row_value = null;
        var cell_address = {c: C, r: R}; //获取单元格地址
        var cell = XLSX.utils.encode_cell(cell_address); //根据单元格地址获取单元格
        if (sheet1[cell]) //获取单元格值
          row_value = sheet1[cell].v;
        else
          row_value = '';
        row.push(row_value);
      }
      //判断整行是否都为空，是则去掉
      for (var i = 0; i < row.length; i++) {
        if (row[i] != '') {
          flag = true;
          break;
        }
      }
      if (flag) data.push(row);
    }
  } catch (e) {
    err = '解析出错' + e.toString();
  }

  callback(err, data);
}

function setAllToZero(data) {
  var grade = require("./mark_model").grade

  for (var j = 1; j < data.length; j++) {
    grade.create({
      exam_id: "5bbcc04e8db9e0582d72dbff", //id2
      grades: ["", "", "", "", "", "", ""],
      studentId: data[j][3],
      name: data[j][4]
    }, (error, doc) => {
      if (error) {
        console.log(error)
      } else {
        console.log({
          error_no: 0,
          error_msg: "ok",
          grade: doc
        })
      }
    })
  }
}

var grade = require("./mark_model").grade
var banji = [] //).sort({'_id':-1}).limit(1).exec(
var stn = 0
var getGrade = (data, j) => {
  console.log(j)
  console.log(data[j])
  console.log(banji)
  console.log(stn)
  grade.findOne({
    exam_id: "5bbcc04e8db9e0582d72dbff", //id2
    studentId: data[j][3],
    name: data[j][4]
  }, function (error, doc) {
    if (error) {
      console.log(err)
    } else {
      if (doc) {
        stn += 1
        if (!banji.includes(data[j][2])) {
          banji.push(data[j][2])
        }
      }
      getGrade(data, j + 1)

    }
  })
}


importExcel('2018.xlsx', function (err, data) {
  if (err) {
    console.log(err);
  } else {
    // console.log(data)
    getGrade(data, 1)
  }
})
